package com.api.gbhg.daizhishun.dao;

import com.api.gbhg.daizhishun.Vo.InDetailExpenseAccountVo;
import com.api.gbhg.daizhishun.Vo.NewDemandVo;
import com.api.gbhg.daizhishun.util.GenerateBudgetJobOrder;
import com.api.gbhg.daizhishun.util.ReadProperties;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;

import java.io.IOException;
import java.util.*;

/**
 * @description: 会计角色待推送-列表查询
 * @author: daizhishun
 * @date: 2021/4/17
 **/

public class AccountingRoleDaoImpl implements AccountingRoleDao {
    public static final Logger logger = LoggerFactory.getLogger(AccountingRoleDaoImpl.class);

    //生成预算执行通知单号
    GenerateBudgetJobOrder GG = new GenerateBudgetJobOrder();
    //创建读取配置文件的所有报销单流程
    ReadProperties read = new ReadProperties();






    //根据workflowid查询表名的通用方法
    @Override
    public String gettablename(String workflowid) {
        String tablename = "select wb.tablename from workflow_bill AS wb where wb.id=\n" +
                " (Select wa.formid from workflow_base AS wa where\n" +
                "wa.id=" + workflowid + ")";
        RecordSet rs = new RecordSet();
        rs.executeQuery(tablename);
        rs.next();
        return rs.getString("tablename");
    }
    //根据requestid查询workflowid通用方法
    @Override
    public String gettablenameByRequestid(Object requestid) {
        String workflowid ="select workflowid from workflow_requestbase as wq where wq.requestid = "+requestid+"";
        RecordSet rs = new RecordSet();
        rs.executeQuery(workflowid);
        rs.next();
        return rs.getString("workflowid");
    }
    //查询表名的通用方法2,返回Object
    public String gettablename2(Object workflowid) {
        String tablename = "select wb.tablename from workflow_bill AS wb where wb.id=\n" +
                " (Select wa.formid from workflow_base AS wa where\n" +
                "wa.id=" + workflowid + ")";
        RecordSet rs = new RecordSet();
        rs.executeQuery(tablename);
        rs.next();
        return rs.getString("tablename");
    }



    //将前端传过来的requestid循环更改支付状态：1：已支付
    @Override
    public boolean updateDetailsBylist(List requestid) {
        if (requestid.size()==0) {
            return false;
        }else {
            for (int i = 0; i < requestid.size(); i++) {
                RecordSet rs = new RecordSet();
                String str = "UPDATE uf_cashierrole set zfzt=1 where requestid="+requestid.get(i)+"";
                rs.executeUpdate(str);
            }
            return true;
        }
    }
    @Override
    public boolean SelectCashierRole(List requestid) throws IOException  {
        AccountingRoleDaoImpl accountingRoleDao = new AccountingRoleDaoImpl();
        GenerateBudgetJobOrder GG = new GenerateBudgetJobOrder();
        ReadProperties properties = new ReadProperties();
        boolean flag = true;
        for (int i = 0;i<requestid.size();i++) {
            Object workflowid = accountingRoleDao.gettablenameByRequestid(requestid.get(i));
            Object tablename = accountingRoleDao.gettablename2(workflowid);
            String result = GG.result();

            if (workflowid.toString().equals(read.TrainingExpense())){
                //培训报销
                RecordSet rs = new RecordSet();
                String str139 = "INSERT INTO uf_cashierrole(requestid,yhmc,yhzh,bxdbh,jbr,ysly,ysxm,zfl,bxrq,glh,bmjjzcfl,dfje,skr,yszxtzdh)\n" +
                        "select\n" +
                        "se.requestid AS requestId,\n" +
                        "pxs.fkyx AS yhmc,\n" +
                        "pxs.fkzh AS yhzh,\n" +
                        "px.lcbh AS bxdbh,\n" +
                        "name.lastname AS jbr,\n" +
                        "px.ysly AS ysly,\n" +
                        "jingfei.xmmc AS ysxm,\n" +
                        "px.zfl AS zfl,\n" +
                        "px.sqrq AS bxrq,\n" +
                        "px.glh,\n" +
                        "jingfei.xmmc AS bmjjzcfl,\n" +
                        "pxs.zfje AS dfje,\n" +
                        "pxs.skr,'"+result+"' As yszxtzdh\n" +
                        "from "+tablename+" AS px\n" +
                        "left join " +tablename+"_dt2 AS pxs on px.id = pxs.mainid\n" +
                        "left join HrmResource AS name ON px.jbr = name.id\n" +
                        "LEFT JOIN workflow_requestbase se ON se.creater = name.id\n" +
                        "left join uf_jingfei AS jingfei ON px.ysxm = jingfei.requestId\n" +
                        "WHERE se.workflowid = " +workflowid+ "\n" +
                        "AND se.creater = name.id\n" +
                        "and se.requestid = px.requestid\n" +
                        "and px.requestid = "+requestid.get(i)+"\n" +
                        "AND se.status in ("+properties.getPropValue("ReimbursementWorkflowid","Accountingnodename")+")" +
                        "GROUP BY se.requestid,px.lcbh,px.sqrq,name.lastname,jingfei.xmmc,px.glh,px.ysly,px.zfl,pxs.fkyx,pxs.fkzh,pxs.zfje,jingfei.xmmc,px.bxje,pxs.skr,se.workflowid";
                rs.executeQuery(str139);
            }else if (workflowid.toString().equals(read.ReportBonus())){
                //缉私举报奖金报销
                RecordSet rs = new RecordSet();
                String str145 = "INSERT INTO uf_cashierrole(requestid,yhmc,yhzh,bxdbh,jbr,ysly,ysxm,zfl,bxrq,glh,bmjjzcfl,dfje,skr,yszxtzdh)\n" +
                        "SELECT\n" +
                        "se.requestid AS requestId,\n" +
                        "pli.fkyx AS yhmc,\n" +
                        "pli.fkzh AS yhzh,\n" +
                        "pli.lcbh AS bxdbh,\n" +
                        "name.lastname AS jbr,\n" +
                        "pli.ysly AS ysly,\n" +
                        "jingfei.xmmc AS ysxm,\n" +
                        "pli.zfl AS zfl,\n" +
                        "pli.sqrq AS bxrq,\n" +
                        "pli.glh AS glh,\n" +
                        "jingfei.xmmc AS bmjjzcfl,\n" +
                        "pli.zfje AS dfje,\n" +
                        "pli.skr AS skr,'"+result+"' AS yszxtzdh\n" +
                        "from " +tablename+ " AS pli\n" +
                        "left join HrmResource AS name ON pli.jbry = name.id\n" +
                        "LEFT JOIN workflow_requestbase se ON se.creater = name.id\n" +
                        "left join uf_jingfei AS jingfei ON pli.ysxm = jingfei.requestId\n" +
                        "WHERE se.workflowid = " +workflowid+ "\n" +
                        "AND se.creater = name.id\n" +
                        "and se.requestid= pli.requestId\n" +
                        "and pli.requestid = " +requestid.get(i)+"\n" +
                        "AND se.status in ("+properties.getPropValue("ReimbursementWorkflowid","Accountingnodename")+")" +
                        "GROUP BY se.requestid,pli.lcbh,pli.sqrq,name.lastname,jingfei.xmmc,pli.glh,pli.ysly,pli.zfl,pli.fkyx,pli.fkzh,pli.zfje,jingfei.xmmc,pli.bxje,pli.skr,se.workflowid";
                rs.executeQuery(str145);
            } else {
                //通用报销
                //明细表2信息
                RecordSet detail2 = new RecordSet();
                String detail2Sql = "SELECT frist.requestid AS requestid, \n" +
                        "two.fkyx AS yhmc, \n" +
                        "two.fkzh AS yhzh, \n" +
                        "frist.lcbh AS bxdbh, \n" +
                        "frist.jbr AS jbr, \n" +
                        "one.ysly AS ysly, \n" +
                        "one.ysxm AS ysxm, \n" +
                        "one.zfl AS zfl, \n" +
                        "frist.sqrq AS bxrq, \n" +
                        "one.glh AS glh, \n" +
                        "one.bmjjzcfl AS bmjjzcfl, \n" +
                        "two.zfje AS dfje, \n" +
                        "two.skr AS skr, \n" +
                        "'"+result+"' AS yszxtzdh \n" +
                        "from "+tablename+"_dt2 AS two \n" +
                        "left join "+tablename+"_dt1 AS one  on one.zfl=two.zflwb \n" +
                        "left join "+tablename+" AS frist on frist.id=one.mainid \n" +
                        "where frist.requestid="+requestid.get(i);
                detail2.executeQuery(detail2Sql);
                List<NewDemandVo> demandVoList = new ArrayList<>();
                Map<String, NewDemandVo> map = new HashMap<>();
                while (detail2.next()) {
                    NewDemandVo demandVo = new NewDemandVo();
                    demandVo.setRequestid(detail2.getStringNoTrim("requestid"));
                    demandVo.setDfje(detail2.getDouble("dfje"));
                    demandVo.setYhzh(detail2.getStringNoTrim("yhzh"));
                    demandVo.setZfl(detail2.getStringNoTrim("zfl"));
                    demandVo.setYhmc2(detail2.getStringNoTrim("yhmc"));
                    demandVo.setBxdbh(detail2.getStringNoTrim("bxdbh"));
                    demandVo.setJbr(detail2.getStringNoTrim("jbr"));
                    demandVo.setYsly(detail2.getStringNoTrim("ysly"));
                    demandVo.setYsxm(detail2.getStringNoTrim("ysxm"));
                    demandVo.setZfl(detail2.getStringNoTrim("zfl"));
                    demandVo.setBxrq(detail2.getStringNoTrim("bxrq"));
                    demandVo.setGlh(detail2.getStringNoTrim("glh"));
                    demandVo.setBmjjzcfl(detail2.getStringNoTrim("bmjjzcfl"));
                    demandVo.setSkr(detail2.getStringNoTrim("skr"));
                    demandVo.setYszxtzdh(detail2.getStringNoTrim("yszxtzdh"));
                    demandVoList.add(demandVo);
                }

                //支付令、付款银行、付款账号不相同就添加记录
                for(int j = 0; j < demandVoList.size(); ++j) {
                    String key = demandVoList.get(j).getYhzh() + demandVoList.get(j).getYhmc2() + demandVoList.get(j).getZfl();
                    double sumMoney = 0;
                    if (map.containsKey(key)) {
                        sumMoney += map.get(key).getDfje();
                        demandVoList.get(j).setDfje(sumMoney + demandVoList.get(j).getDfje());
                        map.remove(j - 1);
                        map.put(key, demandVoList.get(j));
                    } else {
                        map.put(key, demandVoList.get(j));
                    }
                }

                List<NewDemandVo> returnList = new ArrayList();
                Iterator<Map.Entry<String, NewDemandVo>> it = map.entrySet().iterator();
                while(it.hasNext()) {
                    Map.Entry<String, NewDemandVo> entry = it.next();
                    returnList.add(entry.getValue());
                }
                for (NewDemandVo demandVo : returnList) {
                    //通用报销
                    RecordSet rs = new RecordSet();
                    String str139 = "INSERT INTO uf_cashierrole(requestid,yhmc,yhzh,bxdbh,jbr,ysly,ysxm,zfl,bxrq,glh,bmjjzcfl,dfje,skr,yszxtzdh)" +
                            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    flag = rs.executeUpdate(str139,
                            demandVo.getRequestid(),
                            demandVo.getYhmc2(),
                            demandVo.getYhzh(),
                            demandVo.getBxdbh(),
                            demandVo.getJbr(),
                            demandVo.getYsly(),
                            demandVo.getYsxm(),
                            demandVo.getZfl(),
                            demandVo.getBxrq(),
                            demandVo.getGlh(),
                            demandVo.getBmjjzcfl(),
                            demandVo.getDfje(),
                            demandVo.getSkr(),
                            demandVo.getYszxtzdh());
                }
            }

        }
        accountingRoleDao.updateDetailsBylist(requestid);
        return flag;
    }


    //查询出纳角色流程，报销编号如果一致,报销编号里面的银行名称和银行账号一致
    @Override
    public List<NewDemandVo> SelectTrainingReimbursementByList(Map map) {
        List<NewDemandVo> list = new ArrayList<NewDemandVo>();
        StringBuffer str = new StringBuffer();
        RecordSet rs = new RecordSet();
        str.append("select yh.ykhdw as yhmc1,yhmc,yhzh,bxdbh,SUM(dfje) as dfje,count(1) countMax,\n" +
                "ysly = ( STUFF(( SELECT    ',' + convert(varchar,ysly) \n" +
                "                         FROM      uf_cashierrole\n" +
                "                          WHERE     bxdbh=ca.bxdbh and zfzt=1\n" +
                "                        FOR\n" +
                "                          XML PATH('')), 1, 1, '') ),\n" +
                "yszxtzdh = ( STUFF(( SELECT    ',' + yszxtzdh\n" +
                "                          FROM      uf_cashierrole\n" +
                "                          WHERE     bxdbh=ca.bxdbh and zfzt=1\n" +
                "                       FOR\n" +
                "                          XML PATH('')), 1, 1, '') ),\n" +
                "zfl = ( STUFF(( SELECT    ',' + zfl\n" +
                "                          FROM      uf_cashierrole\n" +
                "                          WHERE     bxdbh=ca.bxdbh and zfzt=1\n" +
                "                        FOR\n" +
                "                          XML PATH('')), 1, 1, '') ),\n" +
                "requestid = ( STUFF(( SELECT    ',' + convert(varchar,requestid) \n" +
                "                         FROM      uf_cashierrole\n" +
                "                         WHERE     bxdbh=ca.bxdbh and zfzt=1\n" +
                "                        FOR\n" +
                "                          XML PATH('')), 1, 1, '') )\n" +
                "FROM uf_cashierrole ca\n" +
                "left join uf_yhzhgltz AS yh on yhmc = yh.id\n" +
                "left join HrmResource AS name on jbr = name.lastname\n"+
                "where zfzt = 1 \n");
                if (StringUtils.isNotEmpty(map.get("year").toString())){
                    str.append(" and YEAR(bxrq) = "+ map.get("year").toString());
                }
                if (StringUtils.isNotEmpty(map.get("dw").toString())){
                    str.append(" and name.subcompanyid1="+map.get("dw").toString());
                }
                if (StringUtils.isNotEmpty(map.get("starTime").toString()) && StringUtils.isNotBlank(map.get("endTime").toString())){
                    str.append(" and bxrq BETWEEN '" + map.get("starTime").toString() + "' and '" + map.get("endTime").toString() + "'");
                }
                if (StringUtils.isNotEmpty(map.get("startperform").toString()) && StringUtils.isNotBlank(map.get("endperform").toString())){
                    str.append(" and yszxtzdh BETWEEN '" + map.get("startperform").toString() + "' and '" + map.get("endperform").toString() + "'");
                }
                str.append(" GROUP BY yh.ykhdw,yhzh,bxdbh,yhmc ");
                logger.info("打印出纳报销记录日志：" + str.toString());
                rs.executeQuery(str.toString());
                NewDemandVo newDemandVo;
                while (rs.next()){
                    newDemandVo= new NewDemandVo();
                    newDemandVo.setYhmc(rs.getString("yhmc1"));
                    newDemandVo.setYhmc2(rs.getString("yhmc"));
                    newDemandVo.setYhzh(rs.getString("yhzh"));
                    newDemandVo.setBxdbh(rs.getString("bxdbh"));
                    newDemandVo.setDfje(rs.getDouble("dfje"));
                    newDemandVo.setMxs(rs.getString("countMax"));
                    newDemandVo.setYsly(rs.getString("ysly"));
                    newDemandVo.setYszxtzdh(rs.getString("yszxtzdh"));
                    newDemandVo.setZfl(rs.getString("zfl"));
                    newDemandVo.setRequestid(rs.getString("requestid"));
                    list.add(newDemandVo);
                }
                return list;
            }


    //点击修改支付令，传入参数到接口
    @Override
    public List<InDetailExpenseAccountVo> SelectTrainingReimbursementByA(Map map) {
        RecordSet rs = new RecordSet();
        List<InDetailExpenseAccountVo> list = new ArrayList<InDetailExpenseAccountVo>();
        String str = "  select bxdbh,bxrq,jbr,zfl,ysly,ysxm,glh,bmjjzcfl,dfje,skr,requestid from uf_cashierrole where \n" +
                " bxdbh = '"+map.get("bxdbh")+"' and\n" +
                " yhzh = '"+map.get("yhzh")+"' and yhmc= '"+map.get("yhmc")+"' ";
        rs.executeQuery(str);
        InDetailExpenseAccountVo in;
        while (rs.next()){
            in = new InDetailExpenseAccountVo();
            in.setBxdbh(rs.getString("bxdbh"));
            in.setBxrq(rs.getString("bxrq"));
            in.setJbr(rs.getString("jbr"));
            in.setZfl(rs.getString("zfl"));
            in.setYsly(rs.getString("ysly"));
            in.setYsxm(rs.getString("ysxm"));
            in.setGlh(rs.getString("glh"));
            in.setBmjjzcfl(rs.getString("bmjjzcfl"));
            in.setDfje(rs.getDouble("dfje"));
            in.setSkr(rs.getString("skr"));
            in.setRequestid(rs.getString("requestid"));
            list.add(in);
        }
        return list;
    }


    //出纳角色更改支付令--报销详情
    @Override
    public boolean updateExpenseAccountZfl(List<Map> list){
        boolean flag = true;
        if(list.size()==0){
            flag = false;
            return flag;
        }else {
        for(int i = 0;i<list.size();i++){
            RecordSet rs = new RecordSet();
            String str = "update uf_cashierrole set zfl= '"+list.get(i).get("zfl")+"' \n" +
                    "where bxdbh = '"+list.get(i).get("bxdbh")+"' \n" +
                    " and bxrq= '"+list.get(i).get("bxrq")+"' and jbr= '"+list.get(i).get("jbr")+"' \n" +
                    " and ysly= '"+list.get(i).get("ysly")+"'  and ysxm= '"+list.get(i).get("ysxm")+"' \n" +
                    " and glh= '"+list.get(i).get("glh")+"'"+
                    " and bmjjzcfl= '"+list.get(i).get("bmjjzcfl")+"'\n" +
                    " and dfje= " +list.get(i).get("dfje")+" " +
                    " and skr = '"+list.get(i).get("skr")+"' and requestid= '" +list.get(i).get("requestid")+ "'";
                   rs.executeUpdate(str);
            }
            return true;
        }
    }

}
